use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Vacancies')
  drop table Vacancies
go

create table Vacancies
(
  Id int identity(1,1) not null,
  VacancyDate datetime not null,
  EmployerID int not null,
  PositionID int not null,
  EmployeeWhoFindCandidatesID int null,
  EmployeeWhoApproveCandidatesID int null,
  TradeMarks nvarchar(255) null,
  SalaryRangeID int null,
  SalaryDate datetime null,
  TrialPeriod nvarchar(255),
  SalaryOnTrialPeriod nvarchar(255),
  TerritoryId int null,
  Comment text,
  ExpirienceYear numeric(4,2) null,
  AgeMin int null,
  AgeMax int null,
  CarID int null,
  Education nvarchar(255) null,
  EducationType nvarchar(255) null,
  SexID int null,
  Position_CompareImportantID int null,
  ExpirienceYear_CompareImportantID int null,
  Age_CompareImportantID int null,
  Car_CompareImportantID int null,
  Sex_CompareImportantID int null
)  
go

alter table Vacancies add constraint PK_Vacancies_ID primary key(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Candidates_EmployeeWhoFindCandidatesID 
      foreign key (EmployeeWhoFindCandidatesID) references Candidates(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Candidates_EmployeeWhoApproveCandidatesID 
      foreign key (EmployeeWhoApproveCandidatesID) references Candidates(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Employers_EmployerID 
      foreign key (EmployerID) references Employers(Id) on delete cascade
go


alter table Vacancies add constraint FK_Vacancies_Employers_PositionID 
      foreign key (PositionID) references Positions(Id) on delete cascade
go


alter table Vacancies add constraint FK_Vacancies_SalaryRanges_SalaryRangeID 
      foreign key (SalaryRangeID) references SalaryRanges(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Territories_TerritoryId 
      foreign key (TerritoryId) references Territories(Id) 
go

alter table Vacancies add constraint FK_Vacancies_HasCar_CarID 
      foreign key (CarID) references HasCar(Id) 
go


alter table Vacancies add constraint FK_Vacancies_SexVariants_SexID 
      foreign key (SexID) references SexVariants(Id) 
go

create nonclustered index IX_Employers_EmployerID on Vacancies
       (EmployerID asc)
go


alter table Vacancies add constraint FK_Vacancies_CompareImportants_Position_CompareImportantID 
      foreign key (Position_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_CompareImportants_ExpirienceYear_CompareImportantID 
      foreign key (ExpirienceYear_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_CompareImportants_Sex_CompareImportantID 
      foreign key (Sex_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_CompareImportants_Car_CompareImportantID 
      foreign key (Car_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_CompareImportants_Age_CompareImportantID 
      foreign key (Age_CompareImportantID) references CompareImportants(Id) 
go

alter table Vacancies add constraint FK_Vacancies_Ages_AgeMin 
      foreign key (AgeMin) references Ages(Id) 
go


alter table Vacancies add constraint FK_Vacancies_Ages_AgeMax 
      foreign key (AgeMax) references Ages(Id) 
go

/*
alter table Vacancies with check add constraint CK_Vacancies_ExpirienceYear_CompareImportantID_Choose
check ((ExpirienceYear is null and ExpirienceYear_CompareImportantID is null) or
       (ExpirienceYear is not null and ExpirienceYear_CompareImportantID is not null))
go


alter table Vacancies with check add constraint CK_Vacancies_Sex_CompareImportantID_Choose
check ((SexID is null and Sex_CompareImportantID is null) or
       (SexID is not null and Sex_CompareImportantID is not null))
go

alter table Vacancies with check add constraint CK_Vacancies_Car_CompareImportantID_Choose
check ((CarID is null and Car_CompareImportantID is null) or
       (CarID is not null and Car_CompareImportantID is not null))
go

alter table Vacancies with check add constraint CK_Vacancies_Age_CompareImportantID_Choose
check ((AgeMin is null and AgeMax is null and Age_CompareImportantID is null) or
       (AgeMin is not null and AgeMax is not null and Age_CompareImportantID is not null))
go

alter table Vacancies with check add constraint CK_Vacancies_Mandatory_choise
check (not(EmployeeWhoFindCandidatesID is null and
	       EmployeeWhoApproveCandidatesID is null))
go
*/